package com.qf.dao.impl;

import com.qf.dao.UserDao;
import com.qf.pojo.User;
import com.qf.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author AzureSky_X
 * @Date 2021/1/5 20:16
 * @Version 1.0
 */
public class UserDaoImpl implements UserDao {
    private QueryRunner queryRunner=new QueryRunner();
    //根据用户名查询用户
    @Override
    public User selectByUsername(String username) {
        String sql="select id,username,password,status from tb_user where username=?";
        User user=null;
        try {
            user = queryRunner.query(DbUtils.getConnection(), sql, new BeanHandler<>(User.class), username);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }
    //查询所有用户信息
    @Override
    public List<User> selectAllUser() {
        String sql="select id,username,password,email,gender,role,active_code,status,birthday from tb_user";
        try {
            List<User> users=queryRunner.query(DbUtils.getConnection(),sql,new BeanListHandler<>(User.class));
            return users;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
    //根据id删除用户
    @Override
    public int deleteUser(int id) {
        String sql="delete from tb_user where id=?";
        try {
            queryRunner.update(DbUtils.getConnection(),sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return 0;
    }
    //修改用户信息
    @Override
    public int updateUser(User user) throws SQLException {
        String sql="update tb_user set username=?,password=?,email=?,gender=?,role=?,active_code=?,status=?,birthday=? where id=?";


            return queryRunner.update(DbUtils.getConnection(),sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getGender(),user.getRole(),user.getActive_code(),user.getStatus(),user.getBirthday(),user.getId());


    }
    //根据id查询用户
    @Override
    public User selectUserById(int id) throws SQLException {
        User user=null;
        String sql="select id,username,password,email,gender,role,active_code,status,birthday from tb_user where id=?";
        user= (User) queryRunner.query(DbUtils.getConnection(),sql,new BeanHandler<>(User.class),id);
        return user;

    }

    //插入用户信息
    @Override
    public int insertUser(User user) {
        String sql="insert into tb_user(username,password,email,gender,role,active_code,status,birthday) values(?,?,?,?,?,?,?,?)";
        try {
            int result=queryRunner.update(DbUtils.getConnection(),sql,user.getUsername(),user.getPassword(),user.getEmail(),user.getGender(),user.getRole(),user.getActive_code(),user.getStatus(),user.getBirthday());
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    //分页查询用户信息
    @Override
    public List<User> selectPage(int startPage, Integer pageSize) throws SQLException {
        String sql="select username,password,email,gender,role,active_code,status,birthday from tb_user limit ?,?";

        return queryRunner.query(DbUtils.getConnection(),sql,new BeanListHandler<>(User.class),startPage,pageSize);

    }
    //查询总条数
    @Override
    public Long selectCount() throws SQLException {
        // count(1) 统计的是第一个字段的行数，为null的行数不统计
        String sql ="select count(1) from tb_user";
        //ScalarHandler:将结果集第一行的某一列放到某个对象中。
        return queryRunner.query(DbUtils.getConnection(),sql,new ScalarHandler<>());

    }


}
